﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WinNemoLib;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace ToppingControl.Models
{
   public class TopCordBomModel
   {
      public class TopCordBom_DS
      {
         public string _Topcrdcd { get; set; }
         public string _Tcrdcd { get; set; }
         public string _Irccrdcd { get; set; }
         public string _Compcd { get; set; }
         public string _Stdform { get; set; }
         public string _Age { get; set; }
      }
      public string[] SEL_CUSTOMER()
      {

         string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
         SqlConnection cnn = new SqlConnection(ConnectionString);
         StringBuilder str = new StringBuilder();

         str.AppendLine(" select manufc from " + Nemo.GetINI("Dbase", "Base") + ".dbo.CUSTOMER group by manufc");

         SqlCommand cmd = new SqlCommand(str.ToString(), cnn);
         cmd.Connection.Open();
         cmd.CommandType = CommandType.Text;


         SqlDataAdapter my = new SqlDataAdapter();
         DataSet ds = new DataSet();
         my.SelectCommand = cmd;
         my.Fill(ds);


         if (ds.Tables[0].Rows.Count <= 0)
         {
            cmd.Connection.Close();
            return null;
         }
         string[] list = new string[ds.Tables[0].Rows.Count];
         for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
         {
            DataRow items = ds.Tables[0].Rows[cuIndex];
            list[cuIndex] = items["manufc"].ToString();
         }
         return list;
      }


      public List<TopCordBom_DS> SEL_TOPCORDBOM(string topcode="")
      {
         
         string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
         SqlConnection cnn = new SqlConnection(ConnectionString);
         StringBuilder str = new StringBuilder();

         str.AppendLine(" select * from " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDBOM ");
         if (StrUtil.IsNotEmpty(topcode) ) str.AppendLine(" where topcrdcd='" + topcode +"'");

         SqlCommand cmd = new SqlCommand(str.ToString(), cnn);
         cmd.Connection.Open();
         cmd.CommandType = CommandType.Text;
     

         SqlDataAdapter my = new SqlDataAdapter();
         DataSet ds = new DataSet();
         my.SelectCommand = cmd;
         my.Fill(ds);

         List<TopCordBom_DS> list = new List<TopCordBom_DS>();
         if (ds.Tables[0].Rows.Count <= 0)
         {
            cmd.Connection.Close();
            return list;
         }
         for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
         {
            DataRow items = ds.Tables[0].Rows[cuIndex];
            TopCordBom_DS model = new TopCordBom_DS();
            model._Topcrdcd = items["topcrdcd"].ToString();
            model._Tcrdcd = items["tcrdcd"].ToString();
            model._Irccrdcd = items["irccrdcd"].ToString();
            model._Compcd = items["compcd"].ToString();
            model._Stdform = items["stdform"].ToString();
            model._Age = items["age"].ToString();
            list.Add(model);
         }
         return list;
      }

      public string[] SEL_COMPOUND()
      {

         string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
         SqlConnection cnn = new SqlConnection(ConnectionString);
         StringBuilder str = new StringBuilder();

         str.AppendLine(" select compcd from " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDBOM group by compcd");

         SqlCommand cmd = new SqlCommand(str.ToString(), cnn);
         cmd.Connection.Open();
         cmd.CommandType = CommandType.Text;


         SqlDataAdapter my = new SqlDataAdapter();
         DataSet ds = new DataSet();
         my.SelectCommand = cmd;
         my.Fill(ds);

         
         if (ds.Tables[0].Rows.Count <= 0)
         {
            cmd.Connection.Close();
            return null;
         }
         string[] list =new string[ds.Tables[0].Rows.Count];
         for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
         {
            DataRow items = ds.Tables[0].Rows[cuIndex];
            list[cuIndex] = items["compcd"].ToString();
         }
         return list;
      }
      public string[] SEL_TOPCRDCD()
      {

         string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
         SqlConnection cnn = new SqlConnection(ConnectionString);
         StringBuilder str = new StringBuilder();

         str.AppendLine(" select topcrdcd from " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDBOM group by topcrdcd");

         SqlCommand cmd = new SqlCommand(str.ToString(), cnn);
         cmd.Connection.Open();
         cmd.CommandType = CommandType.Text;


         SqlDataAdapter my = new SqlDataAdapter();
         DataSet ds = new DataSet();
         my.SelectCommand = cmd;
         my.Fill(ds);


         if (ds.Tables[0].Rows.Count <= 0)
         {
            cmd.Connection.Close();
            return null;
         }
         string[] list = new string[ds.Tables[0].Rows.Count];
         for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
         {
            DataRow items = ds.Tables[0].Rows[cuIndex];
            list[cuIndex] = items["topcrdcd"].ToString();
         }
         return list;
      }

      public Boolean Ins_Data(ConnectionWrapper cnn, TopCordBom_DS incoming)
      {
         try
         {
            StringBuilder comstr = new StringBuilder();
            comstr.AppendLine(" INSERT INTO ");
            comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[TOPCORDBOM]");
            comstr.AppendLine(" ([topcrdcd], [tcrdcd], [irccrdcd], [compcd], [stdform]) ");
            comstr.AppendLine("  VALUES (@topcrdcd, @tcrdcd, @irccrdcd, @compcd, @stdform)");
            

            CommandWrapper cmd = cnn.CreateCommand();
            cmd.CommandText = comstr.ToString();


            cmd.AddParameterStringWithValue("@topcrdcd", incoming._Topcrdcd.Trim());
            //cmd.AddParameterStringWithValue("@plndt", DateUtil.ToInternalDate(incoming._Plandt, WinNemoLib.DateUtil.eLanguage.EN));
            cmd.AddParameterStringWithValue("@tcrdcd", incoming._Tcrdcd);            
            cmd.AddParameterStringWithValue("@irccrdcd", incoming._Irccrdcd);
            cmd.AddParameterStringWithValue("@compcd", incoming._Compcd);
            cmd.AddParameterStringWithValue("@stdform", incoming._Stdform);


            if (cmd.ExecuteNonQuery() <= 0)
            {
               cnn.RollbackTransaction();
               return false;
            }

            return true;
         }
         catch (Exception ex)
         {
            return false;
            throw ex;
         }

      }



   }

 

}